create or replace
package body db_rappel as
	
	function get_rappel_by_id(pRAP_ID rappels.rap_id_rappel%type) 
		return sqlcur as
	resultat sqlcur;
	begin
  
		open resultat for
			SELECT rdv.rdv_titre, o.occ_date_debut, o.occ_heure_debut, o.OCC_HEURE_FIN, r.rap_date_rappel
			FROM RAPPELS r 
			INNER JOIN OCCURENCE o
			ON 
			r.OCC_ID = o.OCC_ID
			AND r.RDV_ID = o.RDV_ID
			INNER JOIN RENDEZ_VOUS rdv
			ON r.RDV_ID = rdv.RDV_ID
			WHERE 
				r.RAP_ID_RAPPEL = pRAP_ID;
		
		return resultat;
		EXCEPTION
		WHEN NO_DATA_FOUND
		THEN
		   /* Return an empty record. */
		   RETURN resultat;
	end get_rappel_by_id;
	
	function get_rappels_by_user(
		puti_login	varchar2) 
		return sqlcur as
		
	resultat sqlcur;
   
	begin
	   open resultat for
		    SELECT rdv.rdv_id, rdv.rdv_titre, o.occ_id, o.occ_date_debut, o.occ_heure_debut, r.rap_id_rappel, r.rap_date_rappel
			FROM RAPPELS r 
			INNER JOIN OCCURENCE o
			ON 
			r.OCC_ID = o.OCC_ID
			AND r.RDV_ID = o.RDV_ID
			INNER JOIN RENDEZ_VOUS rdv
			ON r.RDV_ID = rdv.RDV_ID
			WHERE r.uti_login = puti_login
			ORDER BY
				rdv.rdv_id,
				o.occ_id,
				r.rap_date_rappel;
	   return resultat;
	   
	   EXCEPTION
		WHEN NO_DATA_FOUND
		THEN
		   /* Return an empty record. */
		   RETURN resultat;


	end get_rappels_by_user;
	
	function get_rappels_due_by_user(
		puti_login	varchar2) 
		return sqlcur as
		
	resultat sqlcur;
   
	begin		
		open resultat for
		    SELECT rdv.rdv_id, rdv.rdv_titre, o.occ_id, o.occ_date_debut, o.occ_heure_debut, r.rap_id_rappel
			FROM RAPPELS r 
			INNER JOIN OCCURENCE o
			ON 	r.OCC_ID = o.OCC_ID
			AND r.RDV_ID = o.RDV_ID
			INNER JOIN RENDEZ_VOUS rdv
			ON r.RDV_ID = rdv.RDV_ID
			WHERE 
				r.uti_login = puti_login
			AND 
				r.rap_date_rappel < sysdate
			AND
				r.rap_retour_click is null
			ORDER BY
				r.rap_date_rappel;
	   return resultat;
	   
	   EXCEPTION
		WHEN NO_DATA_FOUND
		THEN
		   /* Return an empty record. */
		   RETURN resultat;
	end get_rappels_due_by_user;
	
	procedure ack_rappel(pID_RAPPEL rappels.rap_id_rappel%type) as
	begin
		update
			rappels
		set
			rap_retour_click = 'ok'
		where
			rap_id_rappel = pID_RAPPEL;
		commit;
	end ack_rappel;
	
	function get_rappels_by_rdv(puti_login	varchar2, r_id varchar2) 
		return integer as
	resultat integer;
   
	begin

		SELECT 
			count(*) into resultat
		FROM RAPPELS r
		WHERE r.uti_login = puti_login
		AND r.rdv_id = r_id;
	   
	   return resultat;
	   
	   EXCEPTION
		WHEN NO_DATA_FOUND
		THEN
		   /* Return an empty record. */
		   RETURN 0;

	end get_rappels_by_rdv;
	
		
	procedure save_rappel	(	pUTI_LOGIN utilisateur.uti_login%type,
								pRDV_ID rendez_vous.rdv_id%type,
								pOCC_ID occurence.occ_id%type,
								pDATE_RAPPEL rappels.rap_date_rappel%type) as
	begin
		insert into rappels
			(rap_id_rappel, uti_login, rdv_id, occ_id, rap_date_rappel, rdv_id_HER_1)
		values
			(SEQ_RAP_ID.nextval, pUTI_LOGIN, pRDV_ID, pOCC_ID, pDATE_RAPPEL, pRDV_ID);
		
		commit;
		
	end save_rappel;
	
	procedure update_rappel	(	pID_RAPPEL rappels.rap_id_rappel%type,
								pDATE_RAPPEL rappels.rap_date_rappel%type) as
	begin
		update
			rappels
		set
			rap_date_rappel = pDATE_RAPPEL
		where
			rap_id_rappel = pID_RAPPEL;
		commit;
		
	end update_rappel;
	
	procedure update_rappels	(	pUTI_LOGIN utilisateur.uti_login%type,
								pRDV_ID rendez_vous.rdv_id%type,
								pOCC_ID occurence.occ_id%type,
								pDATE_RAPPEL rappels.rap_date_rappel%type) as
	begin
		update
			rappels
		set
			rap_date_rappel = pDATE_RAPPEL
		where
			uti_login = pUTI_LOGIN
		and
			rdv_id = pRDV_ID
		and 
			occ_id = pOCC_ID;
			
		commit;
		
	end update_rappels;
	
	function get_rappel_by_occ(
		pid_rdv	rendez_vous.rdv_id%type,
		pid_occ occurence.occ_id%type) 
	return rappels%ROWTYPE as
	
	resultat rappels%ROWTYPE;
	
	begin
	
		SELECT * into resultat
		FROM RAPPELS r
		WHERE 
			r.RDV_ID = pid_rdv
		AND
			r.OCC_ID = pid_occ;
			
		return resultat;
		EXCEPTION
		WHEN NO_DATA_FOUND
		THEN
		   /* Return an empty record. */
		   RETURN resultat;
	end get_rappel_by_occ;
	
	procedure delete_rappel_by_id(pRAP_ID rappels.rap_id_rappel%type) as
	begin
		DELETE
		FROM
			RAPPELS r
		WHERE
			r.rap_id_rappel = pRAP_ID;
		commit;
	end delete_rappel_by_id;
			
	procedure delete_rappels_by_rdv(pRAP_RDV rappels.RDV_ID%type) as
	begin
		DELETE
		FROM
			RAPPELS r
		WHERE
			r.RDV_ID = pRAP_RDV;
		commit;
	end delete_rappels_by_rdv;	

end db_rappel;
/
commit;

show errors;